﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using ERP_RORZE_SYSTEM;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_IQRealTime : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        
        public panel_IQRealTime()
        {
            InitializeComponent();            
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            LoadData();
            IQLastTimeRemain.PopupMenuShowing += IQLastTimeRemain_PopupMenuShowing;            
            iqTestContext.ItemClicked += iqLastTimeRemainContext_ItemClicked;
        }

        void iqLastTimeRemainContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.iqTestContext.Hide();
            switch (e.ClickedItem.Name)
            {
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void IQLastTimeRemain_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.iqTestContext.Show(MousePosition);            
        }        

        private void LoadData()
        {
            //////////////////////////////////////
            /// Calculate Last Quantity IQ
            //////////////////////////////////////
            DataTable remainIQ = new DataTable();
            remainIQ.Columns.Add("IQID", typeof(string));
            remainIQ.Columns.Add("Quantity", typeof(float));
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT IQID, SUM(Remain) AS Quantity"
                    + " FROM [241_IQLastTimeRemain]"
                    + " WHERE LastDate > CONVERT(DATETIME, '2014-06-13 00:00:00', 102)"
                    + " GROUP BY IQID";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    DataRow n = remainIQ.NewRow();
                    n["IQID"] = dataReader["IQID"].ToString();
                    n["Quantity"] = float.Parse(dataReader["Quantity"].ToString());
                    remainIQ.Rows.Add(n);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
            }

            //////////////////////////////////////
            /// Calculate Exported IQ
            //////////////////////////////////////
            DataTable exportedIQ = new DataTable();
            exportedIQ.Columns.Add("IQID", typeof(string));
            exportedIQ.Columns.Add("Quantity", typeof(float));
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT [IQID], SUM([MassQuantity]) AS Quantity"
                    + " FROM [241_RZConsumption]"
                    + " GROUP BY [IQID]";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    DataRow n = exportedIQ.NewRow();
                    n["IQID"] = dataReader["IQID"].ToString();
                    n["Quantity"] = float.Parse(dataReader["Quantity"].ToString());
                    exportedIQ.Rows.Add(n);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
            }

            //////////////////////////////////////
            /// Calculate Imported IQ
            //////////////////////////////////////
            DataTable importedIQ = new DataTable();
            importedIQ.Columns.Add("IQID", typeof(string));
            importedIQ.Columns.Add("Quantity", typeof(float));
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT IQID, ImportedQuantity FROM [VIQImportedQuantity]";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    DataRow n = importedIQ.NewRow();
                    n["IQID"] = dataReader["IQID"].ToString();
                    n["Quantity"] = float.Parse(dataReader["ImportedQuantity"].ToString());
                    importedIQ.Rows.Add(n);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
            }

            //////////////////////////////////////
            /// Calculate Total IQ
            //////////////////////////////////////
            DataTable TotalIQ = new DataTable();
            TotalIQ.Columns.Add("IQID", typeof(string));
            TotalIQ.Columns.Add("LastRemain", typeof(float));
            TotalIQ.Columns.Add("Imported", typeof(float));
            TotalIQ.Columns.Add("Exported", typeof(float));
            TotalIQ.Columns.Add("RealRemain", typeof(float));

            foreach (DataRow last_row in remainIQ.Rows)
            {
                DataRow[] total_remain = TotalIQ.Select("IQID='" + last_row["IQID"].ToString() + "'");
                if (total_remain.Length > 0)
                {
                    foreach (DataRow update_row in total_remain)
                    {
                        update_row.BeginEdit();
                        update_row["LastRemain"] = last_row["Quantity"];
                        update_row["RealRemain"] = (float)update_row["LastRemain"];
                        update_row.EndEdit();
                    }
                }
                else
                {
                    DataRow add_row = TotalIQ.NewRow();
                    add_row["IQID"] = last_row["IQID"];
                    add_row["LastRemain"] = last_row["Quantity"];
                    add_row["Imported"] = 0;
                    add_row["Exported"] = 0;
                    add_row["RealRemain"] = last_row["Quantity"];
                    TotalIQ.Rows.Add(add_row);
                }
            }

            foreach (DataRow import_row in importedIQ.Rows)
            {
                DataRow[] total_import = TotalIQ.Select("IQID='" + import_row["IQID"].ToString() + "'");
                if (total_import.Length > 0)
                {
                    foreach (DataRow update_row in total_import)
                    {
                        update_row.BeginEdit();
                        update_row["Imported"] = import_row["Quantity"];
                        update_row["RealRemain"] = (float)update_row["LastRemain"] + (float)update_row["Imported"];
                        update_row.EndEdit();
                    }
                }
                else
                {
                    DataRow add_row = TotalIQ.NewRow();
                    add_row["IQID"] = import_row["IQID"];
                    add_row["LastRemain"] = 0;
                    add_row["Imported"] = import_row["Quantity"];
                    add_row["Exported"] = 0;
                    add_row["RealRemain"] = import_row["Quantity"];
                    TotalIQ.Rows.Add(add_row);
                }
            }

            foreach (DataRow export_row in exportedIQ.Rows)
            {
                DataRow[] total_export = TotalIQ.Select("IQID='" + export_row["IQID"].ToString() + "'");
                if (total_export.Length > 0)
                {
                    foreach (DataRow update_row in total_export)
                    {
                        update_row.BeginEdit();                        
                        update_row["Exported"] = export_row["Quantity"];
                        update_row["RealRemain"] = (float)update_row["LastRemain"] + (float)update_row["Imported"] - (float)update_row["Exported"];
                        update_row.EndEdit();
                    }
                }
                else
                {
                    DataRow add_row = TotalIQ.NewRow();
                    add_row["IQID"] = export_row["IQID"];
                    add_row["LastRemain"] = 0;
                    add_row["Imported"] = 0;
                    add_row["Exported"] = export_row["Quantity"];
                    add_row["RealRemain"] = 0 - (float)export_row["Quantity"];
                    TotalIQ.Rows.Add(add_row);
                }
            }

            ////////////////////////////////////////
            /// Show Data
            ////////////////////////////////////////            
            if (TotalIQ.Rows.Count > 0)
            {
                gridIQLastTimeRemain.DataSource = TotalIQ;
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("IQID", typeof(string));
                newTable.Columns.Add("LastRemain", typeof(float));
                newTable.Columns.Add("Imported", typeof(float));
                newTable.Columns.Add("Exported", typeof(float));
                gridIQLastTimeRemain.DataSource = newTable;
            }
            IQLastTimeRemain.Columns[0].Caption = "IQ ID";
            IQLastTimeRemain.Columns[1].Caption = "Last Remain Quantity";
            IQLastTimeRemain.Columns[2].Caption = "Imported Quantity";
            IQLastTimeRemain.Columns[3].Caption = "Exported Quantity";
            IQLastTimeRemain.BestFitColumns();           
        }
        
        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }
    }
}
